const query = require('../../utils/query')
// 获取分类名称数据
exports.getCategoryName = async () => {
  let sql = `select category_id, category_name from category`
  let result = await query(sql)
  return result
}

// 检验修改的数据是否重名
exports.checkEditCategoryName = async (category_id, category_name) => {
  let sql = `select category_id from category where category_name = '${category_name}' and category_id != ${category_id}`
  let result = await query(sql)
  return result
}

// 修改数据
exports.changeCategory = async (editedData) => {
  editedData.is_show = editedData.is_show ? 1 : 0
  let { category_id, category_name, sort, is_show, add_date, intro, admin_id } =
    editedData

  let sql = `
    UPDATE category 
    SET 
      category_name = '${category_name}',
      sort = ${sort},
      is_show = ${is_show},
      add_date = ${add_date},
      intro = '${intro}',
      admin_id = ${admin_id}
    WHERE 
      category_id = ${category_id}
  `

  let result = await query(sql)
  return result
}

// 获取所需编辑分类的数据
exports.getEditedCategory = async (category_id) => {
  let sql = `select * from category where category_id = '${category_id}'`
  let result = await query(sql)
  return result
}

// 验证是否重名
exports.checkCategoryName = async (category_name) => {
  let sql = `select category_id from category where category_name = '${category_name}'`
  let result = await query(sql)
  return result
}

// 添加分类
exports.addCategory = async (
  category_name,
  intro,
  is_show,
  add_date,
  sort,
  admin_id
) => {
  let sql = `INSERT INTO category 
    (category_name, intro, is_show, add_date, sort, admin_id)
    VALUES ('${category_name}','${intro}',${is_show},${add_date},${sort},${admin_id})`
  let result = await query(sql)
  return result
}

// 删除选中分类
exports.delSelected = async (category_ids) => {
  let sql = `delete from category where category_id in (${category_ids})`
  let result = await query(sql)
  return result
}

// 删除分类
exports.delCategory = async (category_id) => {
  let sql = `delete from category where category_id = ${category_id}`
  let result = await query(sql)
  return result
}

// 获取分类
exports.getCategory = async (
  page = 1,
  pagesize = 10,
  category_name,
  is_show,
  startDate,
  endDate
) => {
  // 计算分页偏移量
  const offset = (page - 1) * pagesize

  let sql = `
    SELECT c1.*, m2.nickname 
    FROM category c1 
    LEFT JOIN manager m2 ON c1.admin_id = m2.admin_id 
    WHERE 1 
  `
  // 动态添加筛选条件
  if (category_name) {
    sql += ` AND c1.category_name LIKE '%${category_name}%'`
  }
  if (is_show) {
    sql += ` AND c1.is_show = ${is_show}`
  }
  if (startDate && !endDate) {
    sql += ` AND c1.add_date > ${startDate}`
  }
  if (!startDate && endDate) {
    sql += ` AND c1.add_date < ${endDate}`
  }
  if (startDate && endDate) {
    sql += ` AND c1.add_date BETWEEN ${startDate} AND ${endDate}`
  }

  // 统一排序 + 分页
  sql += ` ORDER BY c1.sort ASC LIMIT ${offset}, ${pagesize}`

  // 执行查询
  let result = await query(sql)
  return result
}

// 获取分类数量
exports.getCategoryCount = async (
  category_name,
  is_show,
  startDate,
  endDate
) => {
  let sql = `select count(category_id) as count from category WHERE 1 `

  // 动态添加筛选条件
  if (category_name) {
    sql += ` AND category_name LIKE '%${category_name}%'`
  }
  if (is_show) {
    sql += ` AND is_show = ${is_show}`
  }
  if (startDate && !endDate) {
    sql += ` AND add_date > ${startDate}`
  }
  if (!startDate && endDate) {
    sql += ` AND add_date < ${endDate}`
  }
  if (startDate && endDate) {
    sql += ` AND add_date BETWEEN ${startDate} AND ${endDate}`
  }

  let result = await query(sql)
  return result[0].count
}
